# -*- encoding: utf-8 -*-
"""
@File    : get_other_url.py
@Time    : 2021/8/26 16:22
@Author  : zhouys4
"""
import pymssql
from utils.pack_mysql import MySqLHelper

db = MySqLHelper()


# 将other_url存入数据库
def query_other_url():
    sql = """
        SELECT  convert(nvarchar(2000), OtherURL)  OtherURL,SubmitDate from (
        SELECT
            OtherURL,SubmitDate,
            ROW_NUMBER () OVER ( Partition BY OtherURL ORDER BY SubmitDate ) AS row 
        FROM
            RBSettlementApply      where 
        SubmitDate >= '2015-01-01' and 
        SubmitDate <= '2021-01-01'
        and (
        OtherURL like '%.7z' or 
        OtherURL like '%.rar' or 
        OtherURL like '%.zip' or 
        OtherURL like '%.xls%' or 
        OtherURL like '%.xlsx' or 
        OtherURL like '%.csv' 
        )
    ) as rb
        where 
        rb.row=1
    """
    conn = pymssql.connect(host='10.99.60.212', user='i_icac_read', password='sA3#kb15', database='RMS')
    cur = conn.cursor()
    cur.execute(sql)
    rb_data = cur.fetchall()
    sql3 = """
    insert into get_other_url 
    (other_url,submit_date) VALUES (%s,%s)
    """
    ret = db.insertmany(sql3, rb_data, len(rb_data))
    print(ret)
    cur.close()
    conn.close()


# 将amount_detail存入数据库
def query_amount_detail():
    sql = """
        SELECT  A.SettleCode,
             A.PolicyOwnerITCode,
             CONVERT(nvarchar(2000),
             A.OtherURL) OtherURL,
             A.SubmitDate,
             CONVERT(nvarchar(2000),
             C.Name) Name,
             CONVERT(nvarchar(2000),
             C.DepartmentName) DepartmentName,
             CONVERT(nvarchar(2000),
             B.AgentName) AgentName,
             B.Amount,
             A.TotalAmount,
             budgetCode
        FROM [RBSettlementApply] A
        JOIN [RBSettlementApplyDetail] B
            ON A.SettleCode = B.SettleCode
        JOIN ProjectPackage C
            ON A.PackageID = C.PackageID
        WHERE A.Type != 0
            AND B.Amount > 10000
            AND A.OtherURL is NOT null
            AND A.TotalAmount > 300000
            AND (A.OtherURL LIKE '%.7z'
            OR A.OtherURL LIKE '%.rar'
            OR A.OtherURL LIKE '%.zip'
            OR A.OtherURL LIKE '%.xls%'
            OR A.OtherURL LIKE '%.xlsx')
    """
    conn = pymssql.connect(host='10.99.60.212', user='i_icac_read', password='sA3#kb15', database='RMS')
    cur = conn.cursor()
    cur.execute(sql)
    rb_data = cur.fetchall()
    sql3 = """
    insert into amount_detail 
    (settle_code,policy_owner_it_code,other_url,
    submit_date,type_name,department_name,agent_name,
    amount,total_amount,budget_code) 
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
    """
    ret = db.insertmany(sql3, rb_data, len(rb_data))
    print(ret)
    cur.close()
    conn.close()


if __name__ == '__main__':
    query_amount_detail()
